#!/usr/bin/perl
# reads the output of a gammu command and converts it into insert statements

use strict;
use warnings;

# insert syntax
# insert into $sms_table values ('$smsid', '$folder', '$timestamp', '$number', '$message');

# find the name of the sms_table
my $sms_table = "";
my $line;
if (open (my $conf, "<", "dbconf.sh")) {
	# look for the sms_table line
	while (<$conf>) {
		if (m/^\s*sms_table\s*=\s*"(.*)"/) {
			$sms_table=$1 if $1;
			last;
		}
		$sms_table="Inbox" if (! "$sms_table");
	}
	close($conf);
} else {
	$sms_table="Inbox";
}

my @sms;	#list of sms.

my ($i, $j);
$i=0;
while ($line = <>) {
	# skip any leading blank lines
	if ($line =~ m/^$/) { next; }

	# skip ending
	if ($line =~ m/^[[:digit:]]* SMS parts in [[:digit:]]* SMS sequences$/) { last; }

	my %msg = (
		location => "",
		folder => "",
		memstore => "",
		smscnumber => "",
		sentdate => "",
		name => "",
		coding => "",
		rnumber => "",
		status => "",
		message => "",
	);
	# Location line
	if ($line =~ m/^Location ([[:digit:]]*), folder "([\w\s]*)", ([\w\s]*)/) {
		$msg{"location"} = $1 if $1;
		$msg{"folder"} = $2 if $2;
		$msg{"memstore"} = $3 if $3;
		$line = <>; # next line
	}
	#skip SMS Message line
	if ($line =~ m/^SMS message$/) {
		$line = <>; # next line
	}
	#SMSC number line
	if ($line =~ m/^SMSC number\s*:\s*"(.*)"$/) {
		$msg{"smscnumber"} = $1 if $1;
		$line = <>; # next line
	}
	#Sent date line (note, the file should have been preprocessed into a mysqlish timestamp already!)
	#if ($line =~ m/^Sent\s*:\s*/) {
	if ($line =~ m/^[[:digit:]]{4}(-[[:digit:]]{2}){2} ([[:digit:]]{2}:){2}[[:digit:]]{2}$/) {
		$line =~ s/\n$//;
		$msg{"sentdate"} = $line; #run this through date-to-timestamp.pl
		$line = <>; # next line
	}
	#Sender name line
	if ($line =~ m/^Name\s*:\s*"(.*)"$/) {
		$msg{"name"} = $1 if $1;
		$line = <>;
	}
	#SMS coding line
	if ($line =~ m/^Coding\s*:\s*(.*)$/) {
		$msg{"coding"} = $1 if $1;
		$line = <>;
	}
	#Sender number line
	if ($line =~ m/^Remote number\s*:\s*"(.*)"$/) {
		$msg{"rnumber"} = $1 if $1;
		$line = <>;
	}
	#SMS status line and message
	if ($line =~ m/^Status\s*:\s*(\w*)$/) {
		$msg{"status"} = $1 if $1;

		# skip 1 blank line
		$line = <>;

		# get the next data line.
		$msg{"message"} = <>;

		# keep appending data to the line until we reach one of:
		#  - EOF,
		# __ SMS in parts in __ sequences,
		# or another blank line (most common case)
		while ($line = <>) {
			if (($line =~ m/^$/) || ($line =~ m/^[[:digit:]]* SMS parts in [[:digit:]]* SMS sequences$/)) {
				last;
			} else {
				$msg{"message"} .= $line;
			}
		}

		# chop off newline from end off message
		$msg{"message"} =~ s/\n$//g;

		# sanitize message string
		$msg{"message"} =~ s/\n/ /g; #convert newlines to \n
		$msg{"message"} =~ s/\\/\\\\/g; #quote backslash
		$msg{"message"} =~ s/'/\\'/g; #quote single quotes
		$msg{"message"} =~ s/"/\\"/g; #quote double quotes

		# create our sms object
		push (@sms, {%msg});

	}
}

# sort messages by date.
@sms = sort {$a->{sentdate} cmp $b->{sentdate}} @sms;

foreach my $msg (@sms) {
	# insert "Inbox" texts.
	if ($msg->{folder} ~~ "Inbox") {
		print "Insert into $sms_table values ('','$msg->{folder}','$msg->{sentdate}','$msg->{rnumber}','$msg->{message}');\n";
	}
}
